﻿using CSharp.Libs.Database;
using System;
using System.Collections;
using System.Data;

namespace CSharp.Libs.Media
{
    public class DataAccessLayer : IDisposable
    {
        #region declarations
        private SQLiteHandler _sh;
        #endregion



        #region constructor
        public DataAccessLayer(
            string DatabaseFullFileName)
        {
            _sh = new SQLiteHandler(DatabaseFullFileName);
        }
        public DataAccessLayer(
            SQLiteHandler db)
        {
            _sh = db;
        }

        #region dispose pattern (http://www.codeproject.com/Articles/15360/Implementing-IDisposable-and-the-Dispose-Pattern-P)
        // some fields that require cleanup
        private bool disposed = false; // to detect redundant calls

        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    // clean up managed handles here

                }

                disposed = true;
            }
        }

        public void Dispose()
        {
            Dispose(true);
        }
        #endregion
        #endregion

        
        
        #region private functions
        private int _getTagsForFileObject(
            FileObject fo)
        {
            //prepare SQL var
            string strSimpleSQL = "";

            //first get FileObject from db if exists
            strSimpleSQL = "SELECT * FROM tblFileObjects WHERE Hash = '" + fo.Hash + "'";
            DataTable dtFileObjects = _sh.GetDataTable(strSimpleSQL);
            if (dtFileObjects.Rows.Count != 1)
            {
                //FileObject not in db yet. return 0.
                return 0;
            }
            DataRow drRow = dtFileObjects.Rows[0];
            fo.FileObjectId = (drRow["FileObjectId"] as Nullable<long>).Value;
            fo.FullFileName = drRow["FileName"].ToString();
            fo.Comment = drRow["Comment"].ToString();

            //prepare SQL statement
            strSimpleSQL = "SELECT tblTags.Tag, tblTags.TagId FROM tblTags, tblFileObjectTags, tblFileObjects WHERE tblTags.TagId = tblFileObjectTags.TagId AND tblFileObjectTags.FileObjectId = tblFileObjects.FileObjectId AND (tblFileObjects.Hash = '" + fo.Hash + "')";
            
            //get results as Datatable. Prepared statement return an SQLiteDataReader and you usually don't want to reference System.Data.Sqlite here.
            DataTable dtTags = _sh.GetDataTable(strSimpleSQL);

            if (dtTags.Rows.Count > 0)
            {
                //Clear existing tags before. Maybe a tag was removed from FileObject.
                fo.Tags.Clear();

                //put all tags to FileObject
                foreach (DataRow dr in dtTags.Rows)
                {
                    //get values
                    Nullable<long> intTagId = dr["TagId"] as Nullable<long>;
                    string strTag = dr["Tag"] as string;
                    if (intTagId.HasValue && !string.IsNullOrEmpty(strTag))
                    {
                        fo.Tags.Add(new TagObject() { TagId = intTagId.Value, Tag = strTag });
                    }
                }
            }

            return dtTags.Rows.Count;
        }

        private bool _addFileObjectToDB(
            FileObject fo)
        {
            //first write obkject to DB
            string strPreparedSQL = "INSERT INTO tblFileObjects (`FileName`, `Hash`, `Comment`) VALUES (@prmFileName, @prmHash, @prmComment)";
            ArrayList arlSqlParams = new ArrayList();
            arlSqlParams.Add(new SqlParameter() { Name = "@prmFileName", Type = DbType.String, Value = fo.FullFileName });
            arlSqlParams.Add(new SqlParameter() { Name = "@prmHash", Type = DbType.String, Value =  fo.Hash  });
            arlSqlParams.Add(new SqlParameter() { Name = "@prmComment", Type = DbType.String, Value =  fo.Comment  });

            int intAffectedRowCount = _sh.ExecutePreparedStatement(strPreparedSQL, arlSqlParams);

            if (intAffectedRowCount == 1)
            {
                //then get newly generated ID
                fo.FileObjectId = _sh.LastInsertedRowId;

                return true;
            }

            return false;
        }

        private void _removeFileObjectFromDB()
        {
            //remove all entries in tblFileObjectTags
            //then remove entry in tblFileObjects
            //do it in one transaction if possible

            //maybe cleanup tblTags and wipe non referenced entries? Maybe not cause one might still need the tag later.
            //an own "TagManager" might be useful for managing tags and do jobs like "CleanUp" or show a list of tags with reference count.
        }

        private void _addTagToFileObject(
            FileObject fo,
            TagObject to)
        {
            //prepare SQL var
            string strSQL = "";

            //first look if FileObject is already in db. if not, create it.
            strSQL = "SELECT * FROM tblFileObjects WHERE Hash = '" + fo.Hash + "'";
            using (DataTable dtFileObject = _sh.GetDataTable(strSQL))
            {
                if (dtFileObject.Rows.Count == 0)
                {
                    //After adding to db, Object.Id contains the new id generated by db
                    _addFileObjectToDB(fo);
                }
                else
                {
                    //FileObject exists, so read data
                    DataRow drFileObject = dtFileObject.Rows[0];
                    Nullable<long> intFileObjectId = drFileObject["FileObjectId"] as Nullable<long>;
                    fo.FileObjectId = intFileObjectId.Value;
                    fo.Comment = drFileObject["Comment"].ToString();
                }
            }

            //first look if tag exists. if not, create it and get back new id.
            strSQL = "SELECT * FROM tblTags WHERE Tag LIKE '" + to.Tag + "'";
            using (DataTable dtTag = _sh.GetDataTable(strSQL))
            {
                if (dtTag.Rows.Count==0)
                {
                    //After adding to db, Object.Id contains the new id generated by db
                    _addTagToDb(to);
                }
                else if (to.TagId == -1)
                {
                    //If the Tag exists, but the Id of TagObject is still -1, set the correct Id
                    Nullable<long> lngTagId = dtTag.Rows[0]["TagId"] as Nullable<long>;
                    to.TagId = lngTagId.Value;
                }
            }

            //second look if FileObect is not already tagged with this tag?
            strSQL = "SELECT * FROM tblFileObjectTags WHERE TagId = " + to.TagId + " AND FileObjectId = " + fo.FileObjectId;
            if (!_sh.HasRows(strSQL))
            {
                //then store a combination of FileObjectId and TagId in tblFileObjectTags
                //this time do a prepared statement
                strSQL = "INSERT INTO tblFileObjectTags (TagId, FileObjectId) VALUES (@prmTagId, @prmFileObjectId)";
                ArrayList arlParams = new ArrayList();
                arlParams.Add(new SqlParameter() { Name = "@prmTagId", Type = DbType.Int64, Value = to.TagId });
                arlParams.Add(new SqlParameter() { Name = "@prmFileObjectId", Type = DbType.Int64, Value = fo.FileObjectId });

                int intAffectedRowCount = _sh.ExecutePreparedStatement(strSQL, arlParams);

                if (intAffectedRowCount == 1)
                {
                    //success! do something?
                }
                else
                {
                    //no success. do something else?
                }
            }
        }

        private void _removeTagFromFileObject(
            FileObject fo,
            TagObject to)
        {
            //simply remove entry from tblFileObjectTags?
            //or have a look if no other tags exist and delete FileObject in tblFileObjects?
            string strPreparedSQL = "DELETE FROM tblFileObjectTags WHERE TagId = @prmTagId AND FileObjectId = @prmFileObjectId)";
            ArrayList arlParams = new ArrayList();
            arlParams.Add(new SqlParameter() { Name = "@prmTagId", Type = DbType.Int64, Value = to.TagId });
            arlParams.Add(new SqlParameter() { Name = "@prmFileObjectId", Type = DbType.Int64, Value = fo.FileObjectId });

            int intAffectedRowCount = _sh.ExecutePreparedStatement(strPreparedSQL, arlParams);

            if (intAffectedRowCount == 1)
            {
                //success. refresh tags.
                this._getTagsForFileObject(fo);
            }
        }

        private bool _addTagToDb(
            TagObject to)
        {
            //first write obkject to DB
            string strPreparedSQL = "INSERT INTO tblTags (`Tag`) VALUES (@prmTag)";
            ArrayList arlSqlParams = new ArrayList();
            arlSqlParams.Add(new SqlParameter() { Name = "@prmTag", Type = DbType.String, Value = to.Tag });

            int intAffectedRowCount = _sh.ExecutePreparedStatement(strPreparedSQL, arlSqlParams);

            if (intAffectedRowCount == 1)
            {
                //then get newly generated ID
                to.TagId = _sh.LastInsertedRowId;

                return true;
            }

            return false;
        }


        /* further more functionality is needed like managing tags (i.e. list all tags including count of references, ...) */
        
        #endregion



        #region methods
        public int GetTagsForFileObject(
            FileObject fo)
        {
            return _getTagsForFileObject(fo);
        }

        public void AddTagToFileObject(
            FileObject fo,
            TagObject to)
        {
            _addTagToFileObject(fo, to);
        }

        public void RemoveTagFromFileObject(
            FileObject fo,
            TagObject to)
        {

            _removeTagFromFileObject(fo, to);
        }
        #endregion
    }
}
